package cn.pconline.autoclub.task;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author chensy
 */
public class ClubPetrolTask {

    private static Logger log = LoggerFactory.getLogger(ClubPetrolTask.class);

    public static void setLog(Logger log) {
        ClubPetrolTask.log = log;
    }
    private String allClubIdSql = "SELECT club_id FROM ac_club";
    private String dayPetrolSql = "SELECT sum(petrol) as sum_petrol FROM ac_club_petrol_log WHERE club_id = ? AND create_at >= ? AND create_at <= ?";
    private String countClubPick = " select count(1) as num from ac_club_dyna where club_id = ? and pick >0 and type in (1,4) ";
    private String countClubRecommend = " select count(1) as num from ac_club_dyna where club_id = ? and recommend =1 and type in (1,4)";
    private String countClubCheckPetrol = " select sum(petrol) as sum_petrol from ac_member_checkin_record where club_id = ? and create_at >= ? and create_at <= ? ";
    private String updateStatDayPetrolSql = "UPDATE ac_club_stat s SET s.day_petrol = ?, s.day_petrol_update_at = ? WHERE s.club_id = ?";
    private String insertStatPetrolSqlforDay = "INSERT INTO ac_club_stat(club_id,day_petrol,day_petrol_update_at,update_at) VALUES (?,?,?,?)";
    private String insertStatPetrolSqlforWeek = "INSERT INTO ac_club_stat(club_id,week_petrol,week_petrol_update_at,update_at) VALUES (?,?,?,?)";
    private String insertdayPetrolSql = "INSERT INTO ac_club_petrol_daily(club_id,create_at,day_petrol,pick_num,recommend_num,check_petrol) VALUES(?,?,?,?,?,?)";
    //pick_num总精华数 recommend_num总推荐数 check_petrol当天签到油值
    private String updatedayPetrolSql = "UPDATE ac_club_petrol_daily set create_at = ? , day_petrol = ? , pick_num = ? , recommend_num = ? , check_petrol = ?  WHERE club_id = ? and create_at >= ? and create_at <= ? ";
    private String weekPetrolSql = "SELECT sum(day_petrol) as sum_day_petrol FROM ac_club_petrol_daily WHERE create_at >= ? AND create_at <= ? AND club_id = ?";
    private String latestDaySql = "select pick_num,recommend_num,check_petrol from ac_club_petrol_daily where club_id = ? order by create_at desc limit 1";
    private String beforeDaySql = "select pick_num,recommend_num,check_petrol from ac_club_petrol_daily where club_id = ? and create_at <= ? order by create_at desc limit 1";
    private String checkPetrolSql = " select sum(check_petrol) as sum_petrol from ac_club_petrol_daily where club_id = ? and create_at >= ?  and create_at <= ? ";
    private String updateStatWeekPetrolSql = "UPDATE ac_club_stat s SET s.week_petrol = ?,s.week_petrol_update_at = ? WHERE s.club_id = ?";
    //-----
    private String getTotalPetrolSql = "select total_petrol FROM ac_club_petrol WHERE club_id = ?";
//    private String getLevelIdSql = "select level_id from ac_club WHERE club_id = ?";
    private String allClubLevelSql = "select club_id,level_id from ac_club ";
    private String getTotalTopicNumSql = "SELECT COUNT(1) num FROM ac_club_dyna WHERE club_id = ? and type in (1,4)";
    private String updateClubTotalSql = "UPDATE ac_club c SET c.total_petrol = ?,c.topic_num = ? WHERE c.club_id = ?";
    //同步统计表的油值等级
    private String updateStateInfoSql = "UPDATE ac_club_stat c SET c.level_id = ?,c.level_id_update_at = ?,c.total_petrol = ?, c.total_petrol_update_at = ? WHERE c.club_id = ?";
    private String insertStatPetrolSqlforTotal = "INSERT INTO ac_club_stat(club_id,total_petrol,total_petrol_update_at,update_at,level_id,level_id_update_at) VALUES (?,?,?,?,?,?)";
    //----------------排名统计相关--start
    private String updateRankSql = "UPDATE ac_club c SET  c.day_rank=c.rank,c.rank = ? WHERE c.club_id = ?";
//    private String allRankClubIdSql = "SELECT club_id,level_id,total_vip FROM ac_club_stat where level_id > 0 ";
    private String allRankClubIdSql = "SELECT stat.club_id,stat.level_id,stat.total_vip FROM ac_club_stat stat left join ac_club club on stat.club_id = club.club_id  where stat.level_id > 0 order by club.total_petrol desc,club.create_at";
    //private String getRankSql = "SELECT 1 + COUNT(s2.club_id) AS rank,s1.level_id AS level_id FROM ac_club_stat s1, ac_club_stat s2 WHERE s1.club_id = ?  AND s2.total_petrol > s1.total_petrol";//不太准确
//    private String groupByRegion = " SELECT region_id FROM ac_club GROUP BY region_id ";
    private String groupByBrand = " SELECT club_id, brand_id FROM ac_club WHERE brand_id > 0 and level_id>0 GROUP BY brand_id ";
    private String groupByProvince = " SELECT club_id, province_id FROM ac_club WHERE province_id > 0 and level_id>0 GROUP BY province_id ";
//    private String allClubByRegion = "SELECT club_id,total_petrol FROM ac_club_stat where region = ?";
    private String allClubByBrand = " SELECT ac_club.club_id AS club_id FROM ac_club,ac_club_stat WHERE ac_club.club_id = ac_club_stat.club_id AND  ac_club.brand_id = ? AND ac_club_stat.level_id > 0 ";
    private String allClubByProvince = " SELECT ac_club.club_id AS club_id FROM ac_club,ac_club_stat WHERE ac_club.club_id = ac_club_stat.club_id AND  ac_club.province_id = ? AND ac_club_stat.level_id > 0 ";
    private String updateRegionRankSql = "UPDATE ac_club c SET c.day_region_rank=c.region_rank,c.region_rank = ? WHERE c.club_id = ?";
    private String updateBrandRankSql = "UPDATE ac_club c SET c.day_brand_rank=c.brand_rank,c.brand_rank = ? WHERE c.club_id = ?";
    private String updateProvinceRankSql = "UPDATE ac_club c SET c.day_province_rank=c.province_rank,c.province_rank = ? WHERE c.club_id = ?";
    private String allIntegralRank = "SELECT  club_integral_id,NAME, petrol, remark FROM ac_club_integral WHERE club_integral_id IN (2,39,40,41,42)";
    //private String initRank = "UPDATE ac_club SET rank = 0 WHERE club_id NOT IN (SELECT club_id FROM ac_club_stat WHERE level_id>0)";
    //private String updateBeforeDayPetrol = "update ac_club_stat set befday_petrol = rank_petrol";
    private String updateRankPetrol = " update ac_club_stat set befday_petrol = rank_petrol,rank_petrol = ? where club_id =?";
    //油值、排名初始化（统计前，这个初始化是针对所有车友会，防止那些level_id>0，但是地区ID，品牌ID被修改为0的车友会排名值不被初始化）
    private String initRank = "UPDATE ac_club a, ac_club_stat b SET a.rank=0,a.day_rank=0,a.region_rank=0,a.day_region_rank=0,a.brand_rank=0,a.day_brand_rank=0,a.province_rank=0,a.day_province_rank=0 WHERE a.club_id = b.club_id and a.level_id = 0";
    private String initRankPetrol = "update ac_club_stat set rank_petrol = 0, befday_petrol = 0  where level_id <= 0 and (rank_petrol > 0 or befday_petrol > 0)";
    //----------------排名统计相关--end

    public static void main(String[] args) throws SQLException, java.text.ParseException {
        ClubPetrolTask task = new ClubPetrolTask();
        //task.dailyClubPetrolTask(new Date());
        task.dailyClubPetrolTask(new java.text.SimpleDateFormat("yy-MM-dd HH").parse("14-7-28 15"));
        //task.weeklyClubPetrolTask(new Date());
        //task.synClubTotalPetrolTask(new Date());
        task.countClubRank(new Date());
    }

    /**
     * 校验车友会总油值，可以根据记录的日增加油值去统计
     *
     * @param cur
     * @throws SQLException
     */
    public void checkClubTotalPetrolTask(Date cur) throws SQLException {
    }

    /**
     * 每天检查同步总油值（顺便还要同步等级，排名需要用到） （顺便还要同步发贴总数，后台查询用到）
     *
     * @param cur
     * @throws SQLException
     */
    public void synClubTotalPetrolTask(Date cur) throws SQLException {
        List<Club> clubIdList = new ArrayList<Club>(1024);
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            log.info("---->统计车友会的帖子数、回帖数、总油值|同步车友会的等级、总油值到统计表---->准备执行……");
            long start = System.currentTimeMillis();
            //查询所有车友会ID和等级ID
            ps = conn.prepareStatement(allClubLevelSql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Club c = new Club();
                c.setClubId(rs.getLong("club_id"));
                c.setLevelId(rs.getLong("level_id"));
                clubIdList.add(c);
            }
            //逐个车友会统计昨天的油值
            for (Club club : clubIdList) {
                long cid = club.getClubId();
                long levelId = club.getLevelId();

                long totalPetrol = 0;
                //获取总油值
                ps = conn.prepareStatement(getTotalPetrolSql);
                ps.setLong(1, cid);
                rs = ps.executeQuery();
                if (rs.next()) {
                    totalPetrol = (rs.getLong("total_petrol"));
                }

                long topicNum = 0;
                //获取发贴总数
                ps = conn.prepareStatement(DBUtils.getSql(getTotalTopicNumSql, "ac_club_dyna", cid));
                ps.setLong(1, cid);
                rs = ps.executeQuery();
                if (rs.next()) {
                    topicNum = (rs.getLong("num"));
                }

                //更新车友会总油值、发帖总数、留言总数
                ps = conn.prepareStatement(updateClubTotalSql);
                ps.setLong(1, totalPetrol);
                ps.setLong(2, topicNum);
                ps.setLong(3, cid);
                ps.executeUpdate();

                //更新统计表里面的总油值、等级
                ps = conn.prepareStatement(updateStateInfoSql);
                ps.setLong(1, levelId);
                ps.setTimestamp(2, DateUtils.getTimeStamp(cur));
                ps.setLong(3, totalPetrol);
                ps.setTimestamp(4, DateUtils.getTimeStamp(cur));
                ps.setLong(5, cid);
                int uc = ps.executeUpdate();
                if (uc == 0) {//没统计记录则新增
                    ps = conn.prepareStatement(insertStatPetrolSqlforTotal);
                    ps.setLong(1, cid);
                    ps.setLong(2, 0);
                    ps.setTimestamp(3, DateUtils.getTimeStamp(cur));
                    ps.setTimestamp(4, DateUtils.getTimeStamp(cur));
                    ps.setInt(5, 0);
                    ps.setTimestamp(6, DateUtils.getTimeStamp(cur));
                    ps.executeUpdate();
                }
            }
            long end = System.currentTimeMillis();
            log.info("总共统计、同步了{}个车友会，耗时{}秒", clubIdList.size(), (end - start) / 1000.0);
            log.info("---->统计车友会的帖子数、回帖数、总油值|同步车友会的等级、总油值到统计表---->执行完毕！");
        } catch (Exception e) {
            log.error("synClubTotalPetrolTask exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }

    private void resetClubRank() throws SQLException {
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        try {
            log.info("---->统计前进行车友会排名、油值重置---->准备执行……");
            long start = System.currentTimeMillis();

            //车友会等级初始化，为了处理降级后排名不清0的情况
            ps = conn.prepareStatement(initRank);
            int clubs = ps.executeUpdate();

            //车友会等级初始化，为了处理降级后活跃度不清0的情况
            ps = conn.prepareStatement(initRankPetrol);
            int stats = ps.executeUpdate();

            long end = System.currentTimeMillis();
            log.info("总共更新了{}个车友会，{}条统计记录，耗时{}秒", clubs, stats, (end - start) / 1000.0);
            log.info("---->统计前进行车友会排名、油值重置---->执行完毕！");
        } catch (Exception e) {
            log.error("resetClubRank exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, null);
        }
    }

    /**
     * 仅给countClubRank 调用
     *
     * @param cur
     * @return
     * @throws SQLException
     */
    private Map<Long, Integer> countClubCountryRank(Date cur) throws SQLException {
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<Long, Integer> petrolMap = new HashMap<Long, Integer>();
        List<Rank> rankList = new ArrayList<Rank>(1024);
        int levlPetrol = 0; //等级积分基数
        int vipPetrol = 0;  //认证人数积分基数
        int pickPetrol = 0;  //精华帖积分基数
        int recommendPetrol = 0;  //推荐贴积分基数
        int checkPetrol = 0;  //签到积分基数
        try {
            log.info("---->统计全国排名---->准备执行……");
            long start = System.currentTimeMillis();
            //从参数配置表查询积分基数
            ps = conn.prepareStatement(allIntegralRank);
            rs = ps.executeQuery();
            while (rs.next()) {
                if (rs.getString("remark").equals("INTERGRL_BASE_1")) {
                    levlPetrol = rs.getInt("petrol");
                } else if (rs.getString("remark").equals("INTERGRL_BASE_2")) {
                    vipPetrol = rs.getInt("petrol");
                } else if (rs.getString("remark").equals("INTERGRL_BASE_3")) {
                    checkPetrol = rs.getInt("petrol");
                } else if (rs.getString("remark").equals("INTERGRL_BASE_4")) {
                    recommendPetrol = rs.getInt("petrol");
                } else if (rs.getString("remark").equals("PICK")) {
                    pickPetrol = rs.getInt("petrol");
                }
            }
            //查询所有参与排名的车友会ID
            ps = conn.prepareStatement(allRankClubIdSql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Rank r = new Rank();
                r.setId(rs.getLong("club_id"));
                r.setPetrol(rs.getInt("level_id") * levlPetrol + rs.getInt("total_vip") * vipPetrol);
                rankList.add(r);
            }
            //循环处理精华、推荐、签到分数
            for (Rank rank : rankList) {
                int nowPick = 0;
                int nowRecommend = 0;
                int oldPick = 0;
                int oldRecommend = 0;
                long clubId = rank.getId();
                int nowPetrol = rank.getPetrol();
                int check = 0;

                //从车友会每天的油值记录表中获取最近的精华总数和推荐总数
                ps = conn.prepareStatement(latestDaySql);
                ps.setLong(1, clubId);
                rs = ps.executeQuery();
                if (rs.next()) {
                    nowPick = rs.getInt("pick_num");
                    nowRecommend = rs.getInt("recommend_num");
                }

                //从车友会每天的油值记录表中获取30天前的精华总数和推荐总数
                ps = conn.prepareStatement(beforeDaySql);
                ps.setLong(1, clubId);
                ps.setTimestamp(2, DateUtils.getSomeDateEnd(cur, -30));
                rs = ps.executeQuery();
                if (rs.next()) {
                    oldPick = rs.getInt("pick_num");
                    oldRecommend = rs.getInt("recommend_num");
                }

                //从车友会每天的油值记录表中获取签到的总油值
                ps = conn.prepareStatement(checkPetrolSql);
                ps.setLong(1, clubId);
                ps.setTimestamp(2, DateUtils.getSomeDateStart(cur, -30));
                ps.setTimestamp(3, DateUtils.getLastDayEnd(cur));
                rs = ps.executeQuery();
                if (rs.next()) {
                    check = rs.getInt("sum_petrol");
                }

                //计算活跃度
                //加上精华分和推荐分
                nowPetrol += (nowPick - oldPick) * pickPetrol;
                nowPetrol += (nowRecommend - oldRecommend) * recommendPetrol;
                nowPetrol += check * checkPetrol;

                //如果计算的活跃度<0时，活跃度=0
                nowPetrol = nowPetrol <= 0 ? 0 : nowPetrol;
                rank.setPetrol(nowPetrol);
                petrolMap.put(clubId, nowPetrol);
            }

            //全国积分排序
            Collections.sort(rankList, new Comparator<Rank>() {
                public int compare(Rank o1, Rank o2) {
                    return (int) (o2.getPetrol() - o1.getPetrol());
                }
            });
            //更新全国排名排名
            for (int i = 0; i < rankList.size(); i++) {
                ps = conn.prepareStatement(updateRankSql);
                ps.setLong(1, i + 1);
                ps.setLong(2, rankList.get(i).getId());
                ps.executeUpdate();
            }
            //将统计的总油值录入数据库
            for (Long key : petrolMap.keySet()) {
                ps = conn.prepareStatement(updateRankPetrol);
                ps.setLong(1, petrolMap.get(key));
                ps.setLong(2, key);
                ps.executeUpdate();
            }
            long end = System.currentTimeMillis();
            log.info("先做一次查询，查出积分基数，再做一次查询，查出所有车友会，然后每个三次查询，两次更新，共统计了{}个车友会，耗时{}秒",
                    rankList.size(), (end - start) / 1000.0);
            log.info("---->统计全国排名---->执行完毕！");
        } catch (Exception e) {
            log.error("countClubCountryRank exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }
        return petrolMap;
    }

    /**
     * 仅给countClubRank 调用
     *
     * @param petrolMap
     * @throws SQLException
     */
    private void countClubProvinceRank(Map<Long, Integer> petrolMap) throws SQLException {
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            log.info("---->统计省份排名---->准备执行……");
            long start = System.currentTimeMillis();
            long clubs = 0;
            //统计省份排行榜
            List<Long> listProvince = new ArrayList<Long>(1024);
            ps = conn.prepareStatement(groupByProvince);
            rs = ps.executeQuery();
            while (rs.next()) {
                listProvince.add(rs.getLong("province_id"));
            }
            if (listProvince != null && !listProvince.isEmpty()) {
                for (Long provinceId : listProvince) {
                    List<Rank> listProvinceRank = new ArrayList<Rank>(1024);
                    //根据省份ID查询所有车友会
                    ps = conn.prepareStatement(allClubByProvince);
                    ps.setLong(1, provinceId);//provinceId
                    rs = ps.executeQuery();
                    while (rs.next()) {
                        Rank r = new Rank();
                        r.setId(rs.getLong("club_id"));
                        r.setPetrol(petrolMap.get(rs.getLong("club_id")));
                        listProvinceRank.add(r);
                    }

                    //省份积分排序
                    Collections.sort(listProvinceRank, new Comparator<Rank>() {
                        public int compare(Rank o1, Rank o2) {
                            return (int) (o2.getPetrol() - o1.getPetrol());
                        }
                    });
                    //更新省份排名
                    for (int i = 0; i < listProvinceRank.size(); i++) {
                        ps = conn.prepareStatement(updateProvinceRankSql);
                        ps.setLong(1, i + 1);
                        ps.setLong(2, listProvinceRank.get(i).getId());
                        ps.executeUpdate();
                    }
                    log.info("{}(only province_id={}) clubs have been rank!", listProvinceRank.size(), provinceId);
                    clubs += listProvinceRank.size();
                }
            } else {
                log.info("按省份ID分组，没有满足条件的车友会！");
            }
            long end = System.currentTimeMillis();
            log.info("先做一次查询，按省份分组，然后每个省份下做一次查询，查出所有车友会，然后对每个车友会计算排名，做一次更新，共统计了{}个车友会，耗时{}秒",
                    clubs, (end - start) / 1000.0);
            log.info("---->统计省份排名---->执行完毕！");

        } catch (Exception e) {
            log.error("countClubProvinceRank exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }

    }

    /**
     * 仅给countClubRank 调用
     *
     * @param conn
     * @param ps
     * @param rs
     * @param petrolMap
     * @throws SQLException
     */
    private void countClubBrandRank(Map<Long, Integer> petrolMap) throws SQLException {
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            log.info("---->统计品牌排名---->准备执行……");
            long start = System.currentTimeMillis();
            long clubs = 0;
            //统计品牌排行榜
            List<Long> listBrank = new ArrayList<Long>(1024);
            ps = conn.prepareStatement(groupByBrand);
            rs = ps.executeQuery();
            while (rs.next()) {
                listBrank.add(rs.getLong("brand_id"));
            }
            if (listBrank != null && !listBrank.isEmpty()) {
                for (Long brandId : listBrank) {
                    List<Rank> listBrandRank = new ArrayList<Rank>(1024);
                    //根据品牌ID查询所有车友会
                    ps = conn.prepareStatement(allClubByBrand);
                    ps.setLong(1, brandId);//brandId
                    rs = ps.executeQuery();
                    while (rs.next()) {
                        Rank r = new Rank();
                        r.setId(rs.getLong("club_id"));
                        r.setPetrol(petrolMap.get(rs.getLong("club_id")));
                        listBrandRank.add(r);
                    }

                    //品牌积分排序
                    Collections.sort(listBrandRank, new Comparator<Rank>() {
                        public int compare(Rank o1, Rank o2) {
                            return (int) (o2.getPetrol() - o1.getPetrol());
                        }
                    });
                    //更新品牌排名排名
                    for (int i = 0; i < listBrandRank.size(); i++) {
                        //由于历史原因，分区排名字段实际也保存品牌排名
                        ps = conn.prepareStatement(updateRegionRankSql);
                        ps.setLong(1, i + 1);
                        ps.setLong(2, listBrandRank.get(i).getId());
                        ps.executeUpdate();
                        ps = conn.prepareStatement(updateBrandRankSql);
                        ps.setLong(1, i + 1);
                        ps.setLong(2, listBrandRank.get(i).getId());
                        ps.executeUpdate();
                    }
                    log.info("{}(only brand_id={}) clubs have been rank!", listBrandRank.size(), brandId);
                    clubs += listBrandRank.size();
                }
            } else {
                log.info("按品牌ID分组，没有满足条件的车友会！");
            }
            long end = System.currentTimeMillis();
            log.info("先做一次查询，按品牌分组，然后每个品牌下做一次查询，查出所有车友会，然后对每个车友会计算排名，做两次更新，共统计了{}个车友会，耗时{}秒",
                    clubs, (end - start) / 1000.0);
            log.info("---->统计品牌排名---->执行完毕！");

        } catch (Exception e) {
            log.error("countClubBrandRank exception!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }

    /**
     * 每天计算一次排名
     * 排名计算规则调整：等级*2000+认证会员数*100+精华帖子数(过去30天)*30+推荐到首页数（过去30天）*200+总签到油值（过去30天）*5
     *
     * 全国排名 品牌排名 省份排名
     *
     * @param cur
     * @throws SQLException
     */
    public void countClubRank(Date cur) throws SQLException {

        //重置排名相关统计，避免降级到0级后，还有排名
        log.info("重置排名开始……");
        resetClubRank();
        log.info("重置排名结束……");
        //统计全国排名|计算活跃度
        log.info("统计全国排名开始……");
        Map<Long, Integer> petrolMap = countClubCountryRank(cur);
        log.info("统计全国排名结束……");
        //统计品牌排名
        log.info("统计品牌排名开始……");
        countClubBrandRank(petrolMap);
        log.info("统计品牌排名结束……");
        //统计省份排名
        log.info("统计省份排名开始……");
        countClubProvinceRank(petrolMap);
//        log.info("统计省份排名开始……");
    }

    /**
     * 统计车友会每天（昨天）增加的油值
     *
     * @param cur 当前日期
     * @throws SQLException
     */
    public void dailyClubPetrolTask(Date cur) throws SQLException {
        List<Long> clubIdList = new ArrayList<Long>(512);
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            log.info("---->每天统计车友会油值---->准备执行……");
            long start = System.currentTimeMillis();

            //查询所有车友会ID
            ps = conn.prepareStatement(allClubIdSql);
            rs = ps.executeQuery();
            while (rs.next()) {
                clubIdList.add(rs.getLong("club_id"));
            }
            int count = 0;
            //逐个车友会统计昨天的油值
            for (Long id : clubIdList) {
                long oneStart = System.currentTimeMillis();
                long dayPetrol = 0;
                //从日志中获取车友会昨天增加的油值（日志表有分表）
                ps = conn.prepareStatement(DBUtils.getSql(dayPetrolSql, "ac_club_petrol_log", DateUtils.getLastDay(cur)));
                ps.setLong(1, id);
                ps.setTimestamp(2, DateUtils.getLastDayStart(cur));
                ps.setTimestamp(3, DateUtils.getLastDayEnd(cur));
                rs = ps.executeQuery();
                if (rs.next()) {
                    dayPetrol = (rs.getLong("sum_petrol"));
                }

                //更新车友会昨天油值（排行统计表）
                ps = conn.prepareStatement(updateStatDayPetrolSql);
                ps.setLong(1, dayPetrol);
                ps.setTimestamp(2, DateUtils.getTimeStamp(cur));
                ps.setLong(3, id);
                int uc = ps.executeUpdate();
                if (uc == 0) {//没统计记录，则创建
                    ps = conn.prepareStatement(insertStatPetrolSqlforDay);
                    ps.setLong(1, id);
                    ps.setLong(2, dayPetrol);
                    ps.setTimestamp(3, DateUtils.getTimeStamp(cur));
                    ps.setTimestamp(4, DateUtils.getTimeStamp(cur));
                    ps.executeUpdate();
                }

                if (dayPetrol == 0) {
                    continue;//如果当天油值没变化就无需增加日记录
                }

                /**
                 * 统计当前精华帖
                 */
                int pickNum = 0;
                ps = conn.prepareStatement(DBUtils.getSql(countClubPick, "ac_club_dyna", id));
                ps.setLong(1, id);
                rs = ps.executeQuery();
                while (rs.next()) {
                    pickNum = (rs.getInt("num"));
                }

                /**
                 * 统计当前推荐贴
                 */
                int recommendNum = 0;
                ps = conn.prepareStatement(DBUtils.getSql(countClubRecommend, "ac_club_dyna", id));
                ps.setLong(1, id);
                rs = ps.executeQuery();
                while (rs.next()) {
                    recommendNum = (rs.getInt("num"));
                }

                /**
                 * 统计昨天签到的油值
                 */
                int checkTotal = 0;
                ps = conn.prepareStatement(DBUtils.getSql(countClubCheckPetrol, "ac_member_checkin_record", DateUtils.getLastDay(cur)));
                ps.setLong(1, id);
                ps.setTimestamp(2, DateUtils.getLastDayStart(cur));
                ps.setTimestamp(3, DateUtils.getLastDayEnd(cur));
                rs = ps.executeQuery();
                if (rs.next()) {
                    checkTotal = (rs.getInt("sum_petrol"));
                }

                //记录车友会昨天统计结果 （每天最多只有一条记录，先更新再插入，可以避免重复执行）
                ps = conn.prepareStatement(updatedayPetrolSql);
                ps.setTimestamp(1, new java.sql.Timestamp(System.currentTimeMillis()));
                ps.setLong(2, dayPetrol);
                ps.setInt(3, pickNum);
                ps.setInt(4, recommendNum);
                ps.setInt(5, checkTotal);
                ps.setLong(6, id);
                ps.setTimestamp(7, DateUtils.getLastDayStart(cur));
                ps.setTimestamp(8, DateUtils.getLastDayEnd(cur));
                uc = ps.executeUpdate();
                if (uc == 0) {
                    ps = conn.prepareStatement(insertdayPetrolSql);
                    ps.setLong(1, id);
                    //统计时间是昨天!!!!
                    ps.setTimestamp(2, DateUtils.getLastDay(cur));
                    ps.setLong(3, dayPetrol);
                    ps.setInt(4, pickNum);
                    ps.setInt(5, recommendNum);
                    ps.setInt(6, checkTotal);
                    ps.executeUpdate();
                }
                long oneEnd = System.currentTimeMillis();
                log.info("统计完第{}个车友会({})，耗时{}秒",++count,id,(oneEnd-oneStart)/1000.0);
            }
            long end = System.currentTimeMillis();
            log.info("先做一次查询，查出所有车友会，然后每个车友会做4次查询和2次更新，共统计了{}个车友会，耗时{}秒",
                    clubIdList.size(), (end - start) / 1000.0);
            log.info("---->每天统计车友会油值---->执行完毕！");
        } catch (Exception e) {
            log.error("dailyClubPetrolTask excpetion!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }

    /**
     * 根据传入的时间统计过去一周的车友会油值
     *
     * @param cur
     * @throws SQLException
     */
    public void weeklyClubPetrolTask(Date cur) throws SQLException {
        List<Long> clubIdList = new ArrayList<Long>(512);
        Connection conn = DBUtils.getConnection();
        boolean oldCommit = DBUtils.getAutoCommitValue(conn);
        conn.setAutoCommit(false);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            log.info("---->每周统计车友会油值---->准备执行……");
            long start = System.currentTimeMillis();

            //查询所有车友会ID
            ps = conn.prepareStatement(allClubIdSql);
            rs = ps.executeQuery();
            while (rs.next()) {
                clubIdList.add(rs.getLong("club_id"));
            }

            //逐个车友会统计上周的油值
            for (Long id : clubIdList) {
                long weekPetrol = 0;
                //统计上周油值
                ps = conn.prepareStatement(weekPetrolSql);
                ps.setTimestamp(1, DateUtils.getLastWeekStart(cur));
                ps.setTimestamp(2, DateUtils.getLastWeekEnd(cur));
                ps.setLong(3, id);
                rs = ps.executeQuery();
                if (rs.next()) {
                    weekPetrol = rs.getLong("sum_day_petrol");
                }

                //记录车友会上周统计结果
                ps = conn.prepareStatement(updateStatWeekPetrolSql);
                ps.setLong(1, weekPetrol);
                ps.setTimestamp(2, DateUtils.getTimeStamp(cur));
                ps.setLong(3, id);
                int uc = ps.executeUpdate();

                if (uc == 0) {//没统计记录，则创建(正常情况下，先运行dailyTask，是有记录的
                    ps = conn.prepareStatement(insertStatPetrolSqlforWeek);
                    ps.setLong(1, id);
                    ps.setLong(2, 0);
                    ps.setTimestamp(3, DateUtils.getTimeStamp(cur));
                    ps.setTimestamp(4, DateUtils.getTimeStamp(cur));
                    ps.executeUpdate();
                }
            }
            long end = System.currentTimeMillis();
            log.info("先做一次查询，查出所有车友会，然后每个车友会做一次查询和一次更新，共统计了{}个车友会，耗时{}秒",
                    clubIdList.size(), (end - start) / 1000.0);
            log.info("---->每周统计车友会油值---->执行完毕！");
        } catch (Exception e) {
            log.error("weeklyClubPetrolTask excpetion!", e);
        } finally {
            conn.commit();
            conn.setAutoCommit(oldCommit);
            DBUtils.releaseConn(conn, ps, rs);
        }
    }
}
